﻿CREATE TABLE [dbo].[Entity](

	[Id] [dbo].[Id] NOT NULL IDENTITY(1000000000, 1), 

    [Name] [dbo].[Name] NULL, 

    [EntityType] [dbo].[EnumerationValue] NOT NULL DEFAULT 0, 

    [Person] BIT NOT NULL DEFAULT 1,

    [NameLast] [dbo].[NameLast] NULL, 

    [NameFirst] [dbo].[NameFirst] NULL, 

    [NameMiddle] [dbo].[NameMiddle] NULL, 

    [NameSuffix] [dbo].[NameSuffix] NULL, 
	
    [UniqueId] [dbo].[BusinessId] NOT NULL, 

	[FederalTaxId] [dbo].[FederalTaxId] NOT NULL,

    [LegacyId] [dbo].[BusinessId] NULL, 
	
    [CreateId] [dbo].[Name] NOT NULL DEFAULT SUSER_NAME () , 

    [CreateDateTime] [dbo].[CreateModifiedDateTime] NOT NULL DEFAULT GETDATE() , 

    [ModifiedId] [dbo].[Name] NOT NULL DEFAULT SUSER_NAME (),

    [ModifiedDateTime] [dbo].[CreateModifiedDateTime] NOT NULL DEFAULT GETDATE(), 

    CONSTRAINT [PK_Entity] PRIMARY KEY ([Id]), 

	CONSTRAINT [UNQ_EntityType_UniqueId] UNIQUE (EntityType, UniqueId),

    CONSTRAINT [CK_Entity_Name] CHECK (
	
		(CASE 
		
				WHEN ((Person = 0) AND (ISNULL (Name, '') != '')) THEN 1
	
				WHEN ((Person = 1) AND ((ISNULL (NameLast, '') != '') AND (ISNULL (NameFirst, '') != '') AND (NameMiddle IS NOT NULL) AND (NameSuffix IS NOT NULL))) THEN 1

				ELSE 0

			END) = 1)

)

GO



CREATE INDEX [IDX_Entity_UniqueId] ON [dbo].[Entity] (UniqueId)

GO

CREATE INDEX [IDX_Entity_FederalTaxId] ON [dbo].[Entity] (FederalTaxId)

GO

CREATE INDEX [IDX_Entity_LegacyId] ON [dbo].[Entity] (LegacyId)

GO
